USE SAS
GO

 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'GetCityDetail')
	BEGIN
		DROP  Procedure  dbo.GetCityDetail
	END
GO

CREATE Procedure [dbo].[GetCityDetail]
	@PKID		varchar(20) = ''
,	@OWNERID	VARCHAR(10) = ''
AS
BEGIN

	declare 
		@sql			varchar(max)


	SELECT	@sql = ''

	IF NOT EXISTS (SELECT 1 FROM TR_CITY WHERE CityNo  = @PKID)
		SET @PKID = ''


	;WITH A AS (
		SELECT 
			C.CityNo			
		,	c.CityName			
		,	c.IsActive
		,	p.PartnerNo		AS UserID
		FROM TR_CITY as c WITH (NOLOCK)
		INNER JOIN TR_PARTNER as p WITH (NOLOCK)
			ON p.PartnerGUID = c.UserGUID
		WHERE
			C.CityNo = ISNULL(@PKID, 0)
		AND	p.PartnerNo = @OWNERID
		UNION
		SELECT
			0					AS CityNo
		,	''					AS CityName
		,	1					AS IsActive
		,	@OWNERID			AS UserID
		FROM TR_PARTNER as p WITH (NOLOCK)
		WHERE
			ISNULL(@PKID, '') = ''
	), FinalXML AS (
		SELECT 1 AS ID, N'<DATA>' AS Details 
		UNION
		SELECT 2 AS ID, N'<RESPONSE><RESULT>S</RESULT><MESSAGE></MESSAGE></RESPONSE>'
		UNION
		SELECT
			3 AS ID, 
		+	'<PKID><![CDATA[' + CAST(a.CityNo AS VARCHAR) + ']]></PKID>'
		+	'<CITYNO><![CDATA[' + CAST(a.CITYNO AS VARCHAR) + ']]></CITYNO>'
		+	'<CITYNAME><![CDATA[' + ISNULL(a.CITYNAME,'') + ']]></CITYNAME>'
		+	'<USERID><![CDATA[' + CAST(a.USERID AS VARCHAR) + ']]></USERID>'
		+	'<USERGUID><![CDATA[' + CAST(p.PARTNERGUID AS VARCHAR(40)) + ']]></USERGUID>'
		+	'<ISACTIVE><![CDATA[' + (CASE WHEN ISNULL(a.IsActive,1) = 1 THEN 'X' ELSE '' END) + ']]></ISACTIVE>' 
					AS Details
		FROM A
		LEFT OUTER JOIN TR_PARTNER as p
			ON p.PartnerNo = A.USERID
		UNION
		SELECT 4 AS ID, '</DATA>' AS Details 
	)

	SELECT @sql = @sql + Details
	FROM FinalXML
	Order by ID

	select @sql	AS Detail
END



